#include "mainwindow.h"
#include "ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
   ui(new Ui::MainWindow)
{
    ui->setupUi(this);



   db=QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
db.open();


QSqlQuery query(db);
if(!query.exec("PRAGMA foreign_keys=ON;"))
{
    ui->lineEdit_2->setText("error");
}


\

        query.next();
        query.exec("create table Groupp(gruppa varchar(5) unique);");

         gruppiRTF="152,142,162,112,132,1A2,182,122,192,1B2";
         gruppiFSU="402,422,432,492,472,442";
         listgruppFSU=gruppiFSU.split(",");
        listgruppRTF=gruppiRTF.split(",");
        int grupp=listgruppRTF.size()+listgruppFSU.size();
        for(int i=0;i<listgruppRTF.size();i++)
        {
        query.prepare("insert into Groupp (gruppa) "
                              "values (:gruppa);");
        query.bindValue(":gruppa",listgruppRTF[i]);
        query.exec();
        //query.bindValue(":gruppa",listgruppFSU[i]);
        query.exec();
        }
        for(int i=0;i<listgruppFSU.size();i++)
        {
        query.prepare("insert into Groupp (gruppa) "
                              "values (:gruppa);");
        query.bindValue(":gruppa",listgruppFSU[i]);
        query.exec();
        //query.bindValue(":gruppa",listgruppFSU[i]);
        query.exec();
        }
        query.next();
        query.exec("create table Faculties(faculties varchar(5) unique, gruppa text unique );");
        facultets="RTF,GF,RKF,FSU,UF,FVS,FET,FIT,EF";
       facultetslist=facultets.split(",");

        for(int i=0;i<facultetslist.size();i++)
        {
            query.prepare("insert into Faculties (faculties) "
                                  "values (:gruppa);");
            query.bindValue(":faculties",facultetslist[i]);
            query.exec();
        }
        query.next();
       query.exec("update Faculties set gruppa='"+gruppiRTF+"' where faculties='RTF';");
            query.exec();

            query.next();
           query.exec("update Faculties set gruppa='"+gruppiFSU+"' where faculties='FSU';");
                query.exec();

        if(!query.exec("create table information1(familia varchar(20),imia varchar(20), otchestvo varchar(20),pasport text,"
                       "facultet varchar(5) references Faculties(faculties) , gruppa varchar(5) references Groupp(gruppa) )"))
        {
          msgBox.setWindowTitle("ERROR");
              msgBox.setText("error BD");
              msgBox.setIcon(QMessageBox::Information);
              msgBox.setInformativeText("BD not be created");
              msgBox.setStandardButtons(QMessageBox::Ok);
              msgBox.setDefaultButton(QMessageBox::Ok);
              msgBox.exec();
        }

        result=1;

}

MainWindow::~MainWindow()
{
    delete ui;
    db.close();
    delete []mesto_stancii;
    delete File;
    delete model;
}

void MainWindow::connected()
{


}

void MainWindow::on_pushButton_7_clicked()
{
    tablica();

}

void MainWindow::tablica()
{

//    model=new QSqlQueryModel(0);
//   model->setQuery("select * from tablica2");
//        view.setModel(model);
//        view.show();
    QSqlRelationalTableModel *model3=new QSqlRelationalTableModel(0);
    model3->setTable("information1");
    model3->setSort(1, Qt::AscendingOrder);
    model3->select();

        view.setModel(model3);
        view.show();


}

void MainWindow::on_pushButton_3_clicked()
{

     QSqlQuery query(db);
    QString line=ui->lineEdit->text();
    QStringList list=line.split(",");
    if(list.size()==3)
    {
        if(!query.exec("select * from information1 where (familia='"+list[0]+"' and imia= '"+list[1]+"' and otchestvo='"+list[2]+"')"))
    {msgBox.setWindowTitle("ERROR");
        msgBox.setText("Uncorrect enter");
        msgBox.setIcon(QMessageBox::Information);
        msgBox.setInformativeText("");
        msgBox.setStandardButtons(QMessageBox::Ok);
        msgBox.setDefaultButton(QMessageBox::Ok);
        msgBox.exec();

    }
    }

    query.next();
    QString str9=query.value(3).toString();
    QString str10=query.value(4).toString();
    QString str11=query.value(5).toString();
    query.exec("select * from Faculties where (faculties='"+list[0]+")");
    query.exec();
    ui->textEdit->setText("pasportnii danii:"+str9+"\n Facultet:"+str10+"\n Gruppa:"+str11+"");

}

void MainWindow::on_pushButton_clicked()
{QSqlQuery query(db);

    QString str=ui->lineEdit_2->text();
    QStringList list=str.split(",");
    QString *str2=new QString[5];

    if(list.size()==5)
    {
        for(int i=0;i<list.size();i++)
        {
            str2[i]=list[i];
        }
        for(int i=0;i<listgruppRTF.size();i++)
        {
            if(str2[4]==listgruppRTF[i] && str2[3]=="RTF")
            {
                query.next();
                query.prepare("insert into information1 (familia, imia, otchestvo, facultet, gruppa) "
                                      "values (:familia, :imia, :otchestvo, :facultet, :gruppa)");

                query.bindValue(":familia",str2[0]);
                query.bindValue(":imia",str2[1]);
                query.bindValue(":otchestvo",str2[2]);
                       query.bindValue(":facultet",str2[3]);
                query.bindValue(":gruppa",str2[4]);
                query.exec();
            }
            else
                continue;
        }

        for(int i=0;i<listgruppFSU.size();i++)
        {
            if(str2[4]==listgruppFSU[i] && str2[3]=="FSU")
            {
                query.next();
                query.prepare("insert into information1 (familia, imia, otchestvo, facultet, gruppa) "
                                      "values (:familia, :imia, :otchestvo, :facultet, :gruppa)");

                query.bindValue(":familia",str2[0]);
                query.bindValue(":imia",str2[1]);
                query.bindValue(":otchestvo",str2[2]);
                       query.bindValue(":facultet",str2[3]);
                query.bindValue(":gruppa",str2[4]);
                query.exec();
            }
            else
                continue;
        }



    }
    else
    {
        msgBox.setWindowTitle("ERROR");
            msgBox.setText("incorrect entries");
            msgBox.setIcon(QMessageBox::Information);
            msgBox.setInformativeText("Please, Enter a comma-separated \nindex, station name and country ");
            msgBox.setStandardButtons(QMessageBox::Ok);
            msgBox.setDefaultButton(QMessageBox::Ok);
            msgBox.exec();
    }
    delete []str2;
}

void MainWindow::on_pushButton_8_clicked()
{
    QSqlQuery query(db);
    query.next();
    if(!query.exec("drop table information1"))
    {
        msgBox.setWindowTitle("ERROR");
            msgBox.setText("error Removal");
            msgBox.setIcon(QMessageBox::Information);
            msgBox.setInformativeText("BD not be deleted ");
            msgBox.setStandardButtons(QMessageBox::Ok);
            msgBox.setDefaultButton(QMessageBox::Ok);
            msgBox.exec();
    }
    query.exec();
    result=0;
}

void MainWindow::on_pushButton_9_clicked()
{if(result==0)
    {

 QString string3="create table information1(familia varchar(20),imia varchar(20), otchestvo varchar(20),pasport text,"
         "facultet varchar(5) references Faculties(faculties) , gruppa varchar(5) );";
 QSqlQuery query(db);
 if(!query.exec(string3))
 {
   msgBox.setWindowTitle("ERROR");
       msgBox.setText("error BD");
       msgBox.setIcon(QMessageBox::Information);
       msgBox.setInformativeText("BD not be created");
       msgBox.setStandardButtons(QMessageBox::Ok);
       msgBox.setDefaultButton(QMessageBox::Ok);
       msgBox.exec();
 }
    }
    else
    {
        msgBox.setWindowTitle("ERROR");
            msgBox.setText("error BD");
            msgBox.setIcon(QMessageBox::Information);
            msgBox.setInformativeText("The database is still active");
            msgBox.setStandardButtons(QMessageBox::Ok);
            msgBox.setDefaultButton(QMessageBox::Ok);
            msgBox.exec();
    }
    result=1;
}


void MainWindow::on_pushButton_11_clicked()
{
    QString str=ui->lineEdit_3->text();
    QStringList list=str.split(",");
    if(list.size()==3)
    {
    QSqlQuery query(db);
    query.next();
    if(!query.exec("delete from information1 where familia='"+list[0]+"' and imia='"+list[1]+"' and otchestvo='"+list[2]+"';"))
    {
        msgBox.setWindowTitle("ERROR");
            msgBox.setText("error of BD");
            msgBox.setIcon(QMessageBox::Information);
            msgBox.setInformativeText("record can not be deleted \nor does not exist");
            msgBox.setStandardButtons(QMessageBox::Ok);
            msgBox.setDefaultButton(QMessageBox::Ok);
            msgBox.exec();
    }
    query.exec();
}

else
{
msgBox.setWindowTitle("ERROR");
msgBox.setText("error of BD");
msgBox.setIcon(QMessageBox::Information);
msgBox.setInformativeText("record can not be deleted \nor does not exist");
msgBox.setStandardButtons(QMessageBox::Ok);
msgBox.setDefaultButton(QMessageBox::Ok);
msgBox.exec();
}

}

void MainWindow::on_pushButton_5_clicked()
{
    db.close();
    close();
}

void MainWindow::on_pushButton_2_clicked()
{
    QSqlQuery query(db);
    QString str=ui->textEdit_2->toPlainText();
    QStringList list=str.split(",");
    if(list.size()==3)
    {
        if(!query.exec("update information1 set pasport='"+list[2]+"' where imia='"+list[1]+"' and familia='"+list[0]+"';"))
    {
        msgBox.setWindowTitle("ERROR");
            msgBox.setText("error BD");
            msgBox.setIcon(QMessageBox::Information);
            msgBox.setInformativeText("Data entry errors, please re-enter");
            msgBox.setStandardButtons(QMessageBox::Ok);
            msgBox.setDefaultButton(QMessageBox::Ok);
            msgBox.exec();
    }
    }
    else
    {
        msgBox.setWindowTitle("ERROR");
            msgBox.setText("error BD");
            msgBox.setIcon(QMessageBox::Information);
            msgBox.setInformativeText("Data entry errors, please re-enter");
            msgBox.setStandardButtons(QMessageBox::Ok);
            msgBox.setDefaultButton(QMessageBox::Ok);
            msgBox.exec();
    }
}

void MainWindow::on_pushButton_4_clicked()
{
    model=new QSqlQueryModel(0);
   model->setQuery("select * from Faculties");
        view.setModel(model);
        view.show();
}
